#!/usr/bin/env node

// 生产数据迁移到Staging环境脚本
// 使用方法: node scripts/staging/migrate-to-staging.js

const mysql = require('mysql2/promise');
require('dotenv').config();

// 生产环境配置
const prodConfig = {
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
};

// Staging环境配置
const stagingConfig = {
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT),
  user: 'cmb_staging',
  password: 'Staging123!@#',
  database: 'cumrbull_staging'
};

async function migrateData() {
  let prodConnection, stagingConnection;
  
  try {
    console.log('🔄 开始数据迁移...');
    
    // 连接到生产和Staging数据库
    prodConnection = await mysql.createConnection(prodConfig);
    stagingConnection = await mysql.createConnection(stagingConfig);
    
    console.log('✅ 数据库连接成功');
    
    // 获取生产环境的表结构
    const [tables] = await prodConnection.execute(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = ? AND table_type = 'BASE TABLE'`, 
      [prodConfig.database]);
    
    console.log(`📋 发现 ${tables.length} 个表需要迁移`);
    
    for (const table of tables) {
      const tableName = table.table_name;
      console.log(`\n🔄 迁移表: ${tableName}`);
      
      // 获取表结构
      const [createTableResult] = await prodConnection.execute(`SHOW CREATE TABLE ${tableName}`);
      const createTableSQL = createTableResult[0]['Create Table'];
      
      // 在Staging环境中创建表
      await stagingConnection.execute(`DROP TABLE IF EXISTS ${tableName}`);
      await stagingConnection.execute(createTableSQL);
      
      // 复制数据（限制数量以避免过大的数据集）
      const [data] = await prodConnection.execute(`SELECT * FROM ${tableName} LIMIT 1000`);
      
      if (data.length > 0) {
        // 构建插入语句
        const columns = Object.keys(data[0]);
        const placeholders = columns.map(() => '?').join(', ');
        const insertSQL = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
        
        // 批量插入数据
        for (const row of data) {
          const values = columns.map(col => row[col]);
          await stagingConnection.execute(insertSQL, values);
        }
        
        console.log(`   ✅ 已迁移 ${data.length} 条记录`);
      } else {
        console.log(`   ℹ️ 表为空，跳过数据迁移`);
      }
    }
    
    console.log('\n🎉 数据迁移完成！');
    
  } catch (error) {
    console.error('❌ 数据迁移失败:', error.message);
    throw error;
  } finally {
    if (prodConnection) await prodConnection.end();
    if (stagingConnection) await stagingConnection.end();
  }
}

if (require.main === module) {
  migrateData().catch(error => {
    console.error('迁移过程中发生错误:', error);
    process.exit(1);
  });
}

module.exports = { migrateData };
